﻿--11 - Найти контакты дедушек

SELECT DISTINCT P.[ID], P.[LastName], P.[Name],  C.[ContactType], C.[ContactValue]
from [MyFamily].[dbo].[Persons] as P, [MyFamily].[dbo].[Contact] as C
where P.[ID] in
(
SELECT DISTINCT A1.[fk_fatherID]
from [MyFamily].[dbo].[Persons] as A1, [MyFamily].[dbo].[Persons] as A2
where ((A1.ID = A2.fk_fatherID) and (A1.fk_fatherID is not null))
union
SELECT DISTINCT A1.[fk_fatherID]
from [MyFamily].[dbo].[Persons] as A1, [MyFamily].[dbo].[Persons] as A2
where ((A1.ID = A2.fk_motherID) and (A1.fk_fatherID is not null))
)  
and (P.[ID] = C.[fk_PersonID])